package dbs;

import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import domains.User;

/**
 * MySQL 结果集转换为集合
 * 
 * 网上的案例
 * 
 * @author Beanstao@gmail.com
 * @date 2013-10-15上午9:17:48
 * @version 1.0
 */
public class MySQLResultSet2Collection
{

	public static void main(String[] args)
	{
		String url = "jdbc:mysql://localhost:3306/test";
		Connection connection = null;
		try
		{
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			connection = DriverManager.getConnection(url, "root", "root");
			MySQLResultSet2Collection test = MySQLResultSet2Collection.Me();
			Collection<User> collection = test.get(connection, User.class, "select * from users");
			for (Iterator<User> it = collection.iterator(); it.hasNext();)
			{
				User user = it.next();
				System.out.println(user.getId() + ":" + user.getUsername());
			}
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
		finally
		{
			close(connection);
		}
	}

	/**
	 * 转换connection查询结果为指定对象实体集合。
	 */
	public Collection<User> get(final Connection connection, final Class<User> clazz, final String sql)
	{
		PreparedStatement ps = null;
		ResultSet rs = null;
		Collection<User> collection = null;
		try
		{
			// 赋予实例
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			collection = get(rs, clazz);
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
		finally
		{
			close(rs, ps);
		}
		return collection;
	}

	public Collection<User> get(final ResultSet result, final Class<User> clazz)
	{
		Collection<User> collection = null;
		try
		{
			ResultSetMetaData rsmd = result.getMetaData();
			// 获得数据列数
			int cols = rsmd.getColumnCount();
			// 创建等同数据列数的arraylist类型collection实例
			collection = new ArrayList<User>(cols);
			while (result.next())
			{
				User user = null;
				try
				{
					// 从class获得对象实体
					user = clazz.newInstance();
				}
				catch (Exception e)
				{
				}
				// 循环每条记录
				for (int i = 1; i <= cols; i++)
				{
					beanRegister(user, rsmd.getColumnName(i), result.getString(i));
				}
				collection.add(user);
			}
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
		return collection;
	}

	/**
	 * 匹配指定class中数据,并返回包含get和set方法的object
	 */
	private Object[] beanMatch(Class<? extends User> clazz, String beanProperty)
	{
		Object[] result = new Object[2];
		char beanPropertyChars[] = beanProperty.toCharArray();
		beanPropertyChars[0] = Character.toUpperCase(beanPropertyChars[0]);
		String s = new String(beanPropertyChars);
		String names[] = { ("set" + s).intern(), ("get" + s).intern(), ("is" + s).intern(), ("write" + s).intern(), ("read" + s).intern() };
		Method getter = null;
		Method setter = null;
		Method methods[] = clazz.getMethods();
		for (int i = 0; i < methods.length; i++)
		{
			Method method = methods[i];
			// 只取公共字段
			if (!Modifier.isPublic(method.getModifiers()))
				continue;
			String methodName = method.getName().intern();
			for (int j = 0; j < names.length; j++)
			{
				String name = names[j];
				if (!name.equals(methodName))
					continue;
				if (methodName.startsWith("set") || methodName.startsWith("read"))
					setter = method;
				else
					getter = method;
			}
		}
		result[0] = getter;
		result[1] = setter;
		return result;
	}

	/**
	 * 为bean自动注入数据
	 */
	private void beanRegister(User object, String beanProperty, String value)
	{
		Object[] beanObject = beanMatch(object.getClass(), beanProperty);
		Object[] cache = new Object[1];
		Method getter = (Method) beanObject[0];
		Method setter = (Method) beanObject[1];
		try
		{
			// 通过get获得方法类型
			String methodType = getter.getReturnType().getName();
			if (methodType.equalsIgnoreCase("long"))
			{
				cache[0] = new Long(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("int") || methodType.equalsIgnoreCase("integer"))
			{
				cache[0] = new Integer(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("short"))
			{
				cache[0] = new Short(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("float"))
			{
				cache[0] = new Float(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("double"))
			{
				cache[0] = new Double(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("boolean"))
			{
				cache[0] = new Boolean(value);
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("java.lang.String"))
			{
				cache[0] = value;
				setter.invoke(object, cache);
			}
			else if (methodType.equalsIgnoreCase("java.io.InputStream"))
			{
			}
			else if (methodType.equalsIgnoreCase("char"))
			{
				cache[0] = (Character.valueOf(value.charAt(0)));
				setter.invoke(object, cache);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	public static MySQLResultSet2Collection Me()
	{
		return new MySQLResultSet2Collection();
	}

	private static void close(ResultSet rs, PreparedStatement ps)
	{
		try
		{
			if (rs != null)
			{
				rs.close();
				rs = null;
			}
			if (ps != null)
			{
				ps.close();
				ps = null;
			}
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}

	private static void close(Connection connection)
	{
		try
		{
			if (connection != null)
			{
				connection.close();
				connection = null;
			}
		}
		catch (SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}
}
